package org.artifact.core.db;

import cn.hutool.core.util.ArrayUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.log.Log;
import cn.hutool.log.LogFactory;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.artifact.core.cache.CacheEntry;
import org.artifact.core.plugin.datasource.DataSourcePlugin;
import org.artifact.core.server.ServerPlugins;

import javax.sql.DataSource;
import java.math.BigInteger;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Map;
import java.util.function.Function;

public abstract class MySqlDao<T extends AbstractEntity<T>> extends AbstractDao<T> {

    static final Log log = LogFactory.get(MySqlDao.class);

    protected QueryRunner queryRunner;

    protected DataSource getDataSource() {
        return ServerPlugins.me().getPlugin(DataSourcePlugin.class).getDataSource();
    }

    protected abstract String getSaveSql();

    protected abstract String getDeleteSql();


    protected boolean onSave(T t) {
        update(getSaveSql(), (Object[]) t.toSave().write());
        return true;
    }

    @Override
    protected boolean onRemove(T t) {
        update(getDeleteSql(), t.pk());
        return true;
    }

    @Override
    protected boolean onSave(T... t) {
        int l = 0;
        Object[][] params = new Object[t.length][0];
        for (T t1 : t) {
            params[l] = (Object[]) t1.toSave().write();
            l++;
        }
        batchUpdate(getSaveSql(), params);
        return true;
    }

    @Override
    protected boolean onRemove(T... t) {
        int l = 0;
        Object[][] params = new Object[t.length][1];
        for (T t1 : t) {
            params[l][0] = t1.pk();
            l++;
        }
        batchUpdate(getDeleteSql(), params);
        return true;
    }

    @Override
    protected boolean onSave(Collection<T> coll) {
        int l = 0;
        Object[][] params = new Object[coll.size()][0];
        for (T t1 : coll) {
            params[l] = (Object[]) t1.toSave().write();
            l++;
        }
        batchUpdate(getSaveSql(), params);
        return true;
    }

    @Override
    protected boolean onRemove(Collection<T> coll) {
        int l = 0;
        Object[][] params = new Object[coll.size()][1];
        for (T t1 : coll) {
            params[l][0] = t1.pk();
            l++;
        }
        batchUpdate(getDeleteSql(), params);
        return true;
    }

//    @Override
//    protected boolean finishSaveCache() {
//        int l = 0;
//        Object[][] params = new Object[cachedUpdates.size()][0];
//        for (Entry<Object, T> e : cachedUpdates.entrySet()) {
//            params[l] = (Object[]) e.getValue().write();
//            l++;
//        }
//        int[] affectedRows = batchUpdate(getSaveSql(), params);
//        if (affectedRows.length == params.length) {
//            return true;
//        } else {
//            return false;
//        }
//    }
//
//    @Override
//    protected boolean finishDeleteCache() {
//        int l = 0;
//        Object[][] params = new Object[cachedDeletes.size()][1];
//        for (Entry<Object, T> e : cachedDeletes.entrySet()) {
//            params[l][0] = e.getValue().pk();
//            l++;
//        }
//        int[] affectedRows = batchUpdate(getDeleteSql(), params);
//        if (affectedRows.length == params.length) {
//            return true;
//        } else {
//            return false;
//        }
//    }


    @Override
    public List<T> findAll() {
        return resultList(() -> {
            return getCache().getAllOrFunction(()->{
                List<T> ts = find("SELECT * FROM " + getTableName());
                List<CacheEntry<Object, T>> cacheEntrys = new ArrayList<>(ts.size());
                for (T t : ts) {
                    cacheEntrys.add(new CacheEntry(t.pk(), t));
                }
                return cacheEntrys;
            });
        }, t -> true);

    }

    @Override
    public T findByPrimaryKey(Object primaryKey) {
        return resultFirst(() -> {
            return getCache().getOrFunction(primaryKey,k -> {
                return findFirst(MessageFormat.format("SELECT * FROM {0} WHERE {1} = {2}", getTableName(), getPKFieldName(), k.toString()));
            });
        }, t -> ObjectUtil.equal(t.pk(), primaryKey));
    }

    /**
     * 执行sql语句
     *
     * @param sql sql语句
     * @return 受影响的行数
     */
    protected int update(String sql) {
        return update(sql, null);
    }

    /**
     * 执行sql语句
     * <code>
     * executeUpdate("update user set username = 'kitty' where username = ?", "hello kitty");
     * </code>
     *
     * @param sql   sql语句
     * @param param 参数
     * @return 受影响的行数
     */
    protected int update(String sql, Object param) {
        return update(sql, new Object[]{param});
    }

    /**
     * 执行sql语句
     *
     * @param sql    sql语句
     * @param params 参数数组
     * @return 受影响的行数
     */
    protected int update(String sql, Object[] params) {
        queryRunner = new QueryRunner(getDataSource());
        int affectedRows = 0;
        try {
            if (params == null) {
                affectedRows = queryRunner.update(sql);
            } else {
                affectedRows = queryRunner.update(sql, params);
            }
        } catch (SQLException e) {
            log.error(e);
            throw new RuntimeException(e.getMessage());
        }
        return affectedRows;
    }

    /**
     * 执行批量sql语句
     *
     * @param sql    sql语句
     * @param params 二维参数数组
     * @return 受影响的行数的数组
     */
    protected int[] batchUpdate(String sql, Object[][] params) {
        queryRunner = new QueryRunner(getDataSource());
        int[] affectedRows = new int[0];
        try {
            affectedRows = queryRunner.batch(sql, params);
        } catch (SQLException e) {
            log.error(e);
            throw new RuntimeException(e.getMessage());
        }
        return affectedRows;
    }

//    /** 
//     * 执行查询，将每行的结果保存到一个Map对象中，然后将所有Map对象保存到List中 
//     * @param sql sql语句 
//     * @return 查询结果 
//     */ 
//    protected List<Map<String, Object>> find(String sql) { 
//        return find(sql, null); 
//    } 
//      
//    /** 
//     * 执行查询，将每行的结果保存到一个Map对象中，然后将所有Map对象保存到List中 
//     * @param sql sql语句 
//     * @param param 参数 
//     * @return 查询结果 
//     */ 
//    protected List<Map<String, Object>> find(String sql, Object param) { 
//        return find(sql, new Object[] {param}); 
//    } 
//      
//    /** 
//     * 执行查询，将每行的结果保存到一个Map对象中，然后将所有Map对象保存到List中 
//     * @param sql sql语句 
//     * @param params 参数数组 
//     * @return 查询结果 
//     */ 
//    protected List<Map<String, Object>> find(String sql, Object[] params) { 
//        queryRunner = new QueryRunner(getDataSource()); 
//        List<Map<String, Object>> list = new ArrayList<Map<String,Object>>(); 
//        try { 
//            if (params == null) { 
//                list = (List<Map<String, Object>>) queryRunner.query(sql, new MapListHandler()); 
//            } else { 
//                list = (List<Map<String, Object>>) queryRunner.query(sql, new MapListHandler(), params); 
//            } 
//        } catch (SQLException e) { 
//            log.error("Error occured while attempting to query data", e); 
//        } 
//        return list; 
//    } 

    /**
     * 执行查询，将每行的结果保存到Bean中，然后将所有Bean保存到List中
     *
     * @param sql sql语句
     * @return 查询结果
     */
    protected List<T> find(String sql) {
        return find(sql, null);
    }

    /**
     * 执行查询，将每行的结果保存到Bean中，然后将所有Bean保存到List中
     *
     * @param sql   sql语句
     * @param param 参数
     * @return 查询结果
     */
    protected List<T> find(String sql, Object param) {
        return find(sql, new Object[]{param});
    }

    /**
     * 执行查询，将每行的结果保存到Bean中，然后将所有Bean保存到List中
     *
     * @param sql    sql语句
     * @param params 参数数组
     * @return 查询结果
     */
    protected List<T> find(String sql, Object[] params) {
        queryRunner = new QueryRunner(getDataSource());
        List<Object[]> arrays = new ArrayList<Object[]>();
        List<T> list = new ArrayList<T>();
        try {
            if (params == null) {
                arrays = queryRunner.query(sql, new ArrayListHandler());
            } else {
                arrays = queryRunner.query(sql, new ArrayListHandler(), params);
            }
            for (Object[] array : arrays) {
                T t = createEntity();
                t.read(array);
                list.add(t);
            }
            arrays.clear();
            arrays = null;
        } catch (SQLException e) {
            log.error(e);
            throw new RuntimeException(e.getMessage());
        }
        return list;
    }

    /**
     * 查询出结果集中的第一条记录，并封装成对象
     *
     * @param sql sql语句
     * @return 对象
     */
    protected T findFirst(String sql) {
        return findFirst(sql, null);
    }

    /**
     * 查询出结果集中的第一条记录，并封装成对象
     *
     * @param sql   sql语句
     * @param param 参数
     * @return 对象
     */
    protected T findFirst(String sql, Object param) {
        return findFirst(sql, new Object[]{param});
    }

    /**
     * 查询出结果集中的第一条记录，并封装成对象
     *
     * @param sql    sql语句
     * @param params 参数数组
     * @return 对象
     */
    protected T findFirst(String sql, Object[] params) {
        queryRunner = new QueryRunner(getDataSource());
        Object[] array = null;
        T t = null;
        try {
            if (params == null) {
                array = queryRunner.query(sql, new ArrayHandler());
            } else {
                array = queryRunner.query(sql, new ArrayHandler(), params);
            }
            if (ArrayUtil.isNotEmpty(array)) {
                t = createEntity();
                t.read(array);
                array = null;
            }
        } catch (SQLException e) {
            log.error(e);
            throw new RuntimeException(e.getMessage());
        }
        return t;
    }

    @Override
    public boolean existsTable() {
        queryRunner = new QueryRunner(getDataSource());
        boolean isExist = false;
        String sql = "SHOW TABLES IN " + getDBName() + " WHERE Tables_in_" + getDBName() + " = ?";
        try {
            Map<String, Object> rel = queryRunner.query(sql, new MapHandler(), getTableName());
            if (rel != null) {
                isExist = true;
            }
        } catch (SQLException e) {
            log.error(e);
            throw new RuntimeException(e.getMessage());
        }
        return isExist;
    }


    private ScalarHandler scalarHandler = new ScalarHandler() {
        @Override
        public Object handle(ResultSet rs) throws SQLException {
            Object obj = super.handle(rs);
            if (obj instanceof BigInteger)
                return ((BigInteger) obj).longValue();
            return obj;
        }
    };

    /**
     * 执行查询，将每行的结果保存到Bean中，然后将所有Bean保存到List中
     *
     * @return 查询结果
     */
    protected List<T> queryList(Function<String,String> function) {
        queryRunner = new QueryRunner(getDataSource());
        List<Object[]> arrays = new ArrayList<Object[]>();
        List<T> list = new ArrayList<T>();
        try {
            String sql = MessageFormat.format("SELECT * FROM {0}",getTableName());
            sql = function.apply(sql);
            arrays = queryRunner.query(sql, new ArrayListHandler());
            for (Object[] array : arrays) {
                T t = createEntity();
                t.read(array);
                list.add(t);
            }
            arrays.clear();
            arrays = null;
        } catch (SQLException e) {
            log.error(e);
            throw new RuntimeException(e.getMessage());
        }
        return list;
    }

    /**
     * 执行查询，将每行的结果保存到Bean中，然后将所有Bean保存到List中
     *
     * @return 查询结果
     */
    protected long queryListCount(Function<String,String> function) {
        queryRunner = new QueryRunner(getDataSource());
        List<Object[]> arrays = new ArrayList<Object[]>();
        List<T> list = new ArrayList<T>();
        Number num = 0;
        try {
            String sql = MessageFormat.format("SELECT * FROM {0}",getTableName());
            sql = function.apply(sql);
            num = (Number) queryRunner.query(sql, scalarHandler);
        } catch (SQLException e) {
            log.error(e);
            throw new RuntimeException(e.getMessage());
        }
        return (num != null) ? num.longValue() : -1;
    }
}
